<?php

require 'Slim/Slim.php';

$app = new Slim();

$app->get('/listings/', 'getListings');
$app->get('/listings/:id',	'getListing');
$app->get('/listings/search/:query', 'findByName');
$app->post('/listings', 'addListing');
$app->put('/listings/:id', 'updateListing');
$app->delete('/listings/:id',	'deleteListing');

$app->run();

function getListings() {
    $sql = "SELECT * FROM listings WHERE LEFT(RooftopLatitude, 6) IN ('25.833', '25.834', '25.835') ORDER BY address";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$listings = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo json_encode($listings);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getListing($id) {
    $sql = "SELECT * FROM listings WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$listing = $stmt->fetchObject();  
		$db = null;
		echo json_encode($listing); 
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function addListing() {
	error_log('addListing\n', 3, '/var/tmp/php.log');
	$request = Slim::getInstance()->request();
	$listing = json_decode($request->getBody());
	$sql = "INSERT INTO listings (ListingNumber, address, APN, ApxLotSqFt, ApxLotAcres, ApxSqFt, Bedrooms, BedroomDescription, Bathrooms, Community, ConstructionType, Cooling, DesignDescription, DOM, EquipmentAppliances, ExteriorFeatures, FloorDescription, GarageDescription, GarageSpaces, GuestHouseDescription, Heating, HousingForOlderPersonsAct, InteriorFeatures, ListingPrice, LotDescription, MajorCrossStreet, MarketingRemarks, ParkingDescription, PropertyType, RoofDescription, RoomsDescription, SellingPrice, Sewer, Sprinkler, SqFtofGuestHouse, Status, Style, View1, Water, WaterAccess, WaterfrontDescription, WindowsTreatment, YearBuilt) VALUES 
	(:ListingNumber, :address, :APN, :ApxLotSqFt, :ApxLotAcres, :ApxSqFt, :Bedrooms, :BedroomDescription, :Bathrooms, :Community, :ConstructionType, :Cooling, :DesignDescription, :DOM, :EquipmentAppliances, :ExteriorFeatures, :FloorDescription, :GarageDescription, :GarageSpaces, :GuestHouseDescription, :Heating, :HousingForOlderPersonsAct, :InteriorFeatures, :ListingPrice, :LotDescription, :MajorCrossStreet, :MarketingRemarks, :ParkingDescription, :PropertyType, :RoofDescription, :RoomsDescription, :SellingPrice, :Sewer, :Sprinkler, :SqFtofGuestHouse, :Status, :Style, :View1, :Water, :WaterAccess, :WaterfrontDescription, :WindowsTreatment, :YearBuilt)";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("ListingNumber", $listing->ListingNumber);
		$stmt->bindParam("address", $listing->address);
		$stmt->bindParam("APN", $listing->APN);
		$stmt->bindParam("ApxLotSqFt", $listing->ApxLotSqFt);
		$stmt->bindParam("ApxLotAcres", $listing->ApxLotAcres);
		$stmt->bindParam("ApxSqFt", $listing->ApxSqFt);
		$stmt->bindParam("Bedrooms", $listing->Bedrooms);
		$stmt->bindParam("BedroomDescription", $listing->BedroomDescription);
		$stmt->bindParam("Bathrooms", $listing->Bathrooms);
		$stmt->bindParam("Community", $listing->Community);
		$stmt->bindParam("ConstructionType", $listing->ConstructionType);
		$stmt->bindParam("Cooling", $listing->Cooling);
		$stmt->bindParam("DesignDescription", $listing->DesignDescription);
		$stmt->bindParam("DOM", $listing->DOM);
		$stmt->bindParam("EquipmentAppliances", $listing->EquipmentAppliances);
		$stmt->bindParam("ExteriorFeatures", $listing->ExteriorFeatures);
		$stmt->bindParam("FloorDescription", $listing->FloorDescription);
		$stmt->bindParam("GarageDescription", $listing->GarageDescription);
		$stmt->bindParam("GarageSpaces", $listing->GarageSpaces);
		$stmt->bindParam("GuestHouseDescription", $listing->GuestHouseDescription);
		$stmt->bindParam("Heating", $listing->Heating);
		$stmt->bindParam("HousingForOlderPersonsAct", $listing->HousingForOlderPersonsAct);
		$stmt->bindParam("InteriorFeatures", $listing->InteriorFeatures);
		$stmt->bindParam("ListingPrice", $listing->ListingPrice);
		$stmt->bindParam("LotDescription", $listing->LotDescription);
		$stmt->bindParam("MajorCrossStreet", $listing->MajorCrossStreet);
		$stmt->bindParam("MarketingRemarks", $listing->MarketingRemarks);
		$stmt->bindParam("ParkingDescription", $listing->ParkingDescription);
		$stmt->bindParam("PropertyType", $listing->PropertyType);
		$stmt->bindParam("RoofDescription", $listing->RoofDescription);
		$stmt->bindParam("RoomsDescription", $listing->RoomsDescription);
		$stmt->bindParam("SellingPrice", $listing->SellingPrice);
		$stmt->bindParam("Sewer", $listing->Sewer);
		$stmt->bindParam("Sprinkler", $listing->Sprinkler);
		$stmt->bindParam("SqFtofGuestHouse", $listing->SqFtofGuestHouse);
		$stmt->bindParam("Status", $listing->Status);
		$stmt->bindParam("Style", $listing->Style);
		$stmt->bindParam("View1", $listing->View1);
		$stmt->bindParam("Water", $listing->Water);
		$stmt->bindParam("WaterAccess", $listing->WaterAccess);
		$stmt->bindParam("WaterfrontDescription", $listing->WaterfrontDescription);
		$stmt->bindParam("WindowsTreatment", $listing->WindowsTreatment);
		$stmt->bindParam("YearBuilt", $listing->YearBuilt);
		$stmt->execute();
		$listing->id = $db->lastInsertId();
		$db = null;
		echo json_encode($listing); 
	} catch(PDOException $e) {
		error_log($e->getMessage(), 3, '/var/tmp/php.log');
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function updateListing($id) {
	$request = Slim::getInstance()->request();
	$body = $request->getBody();
	$listing = json_decode($body);
	$sql = "UPDATE listings SET ListingNumber=:ListingNumber, address=:address, APN=:APN, ApxLotSqFt=:ApxLotSqFt, ApxLotAcres=:ApxLotAcres, ApxSqFt=:ApxSqFt, Bedrooms=:Bedrooms, BedroomDescription=:BedroomDescription, Bathrooms=:Bathrooms, Community=:Community, ConstructionType=:ConstructionType, Cooling=:Cooling, DesignDescription=:DesignDescription, DOM=:DOM, EquipmentAppliances=:EquipmentAppliances, ExteriorFeatures=:ExteriorFeatures, FloorDescription=:FloorDescription, GarageDescription=:GarageDescription, GarageSpaces=:GarageSpaces, GuestHouseDescription=:GuestHouseDescription, Heating=:Heating, HousingForOlderPersonsAct=:HousingForOlderPersonsAct, InteriorFeatures=:InteriorFeatures, ListingPrice=:ListingPrice, LotDescription=:LotDescription, MajorCrossStreet=:MajorCrossStreet, MarketingRemarks=:MarketingRemarks, ParkingDescription=:ParkingDescription, PropertyType=:PropertyType, RoofDescription=:RoofDescription, RoomsDescription=:RoomsDescription, SellingPrice=:SellingPrice, Sewer=:Sewer, Sprinkler=:Sprinkler, SqFtofGuestHouse=:SqFtofGuestHouse, Status=:Status, Style=:Style, View1=:View1, Water=:Water, WaterAccess=:WaterAccess, WaterfrontDescription=:WaterfrontDescription, WindowsTreatment=:WindowsTreatment, YearBuilt=:YearBuilt WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("ListingNumber", $listing->ListingNumber);
		$stmt->bindParam("address", $listing->address);
		$stmt->bindParam("APN", $listing->APN);
		$stmt->bindParam("ApxLotSqFt", $listing->ApxLotSqFt);
		$stmt->bindParam("ApxLotAcres", $listing->ApxLotAcres);
		$stmt->bindParam("ApxSqFt", $listing->ApxSqFt);
		$stmt->bindParam("Bedrooms", $listing->Bedrooms);
		$stmt->bindParam("BedroomDescription", $listing->BedroomDescription);
		$stmt->bindParam("Bathrooms", $listing->Bathrooms);
		$stmt->bindParam("Community", $listing->Community);
		$stmt->bindParam("ConstructionType", $listing->ConstructionType);
		$stmt->bindParam("Cooling", $listing->Cooling);
		$stmt->bindParam("DesignDescription", $listing->DesignDescription);
		$stmt->bindParam("DOM", $listing->DOM);
		$stmt->bindParam("EquipmentAppliances", $listing->EquipmentAppliances);
		$stmt->bindParam("ExteriorFeatures", $listing->ExteriorFeatures);
		$stmt->bindParam("FloorDescription", $listing->FloorDescription);
		$stmt->bindParam("GarageDescription", $listing->GarageDescription);
		$stmt->bindParam("GarageSpaces", $listing->GarageSpaces);
		$stmt->bindParam("GuestHouseDescription", $listing->GuestHouseDescription);
		$stmt->bindParam("Heating", $listing->Heating);
		$stmt->bindParam("HousingForOlderPersonsAct", $listing->HousingForOlderPersonsAct);
		$stmt->bindParam("InteriorFeatures", $listing->InteriorFeatures);
		$stmt->bindParam("ListingPrice", $listing->ListingPrice);
		$stmt->bindParam("LotDescription", $listing->LotDescription);
		$stmt->bindParam("MajorCrossStreet", $listing->MajorCrossStreet);
		$stmt->bindParam("MarketingRemarks", $listing->MarketingRemarks);
		$stmt->bindParam("ParkingDescription", $listing->ParkingDescription);
		$stmt->bindParam("PropertyType", $listing->PropertyType);
		$stmt->bindParam("RoofDescription", $listing->RoofDescription);
		$stmt->bindParam("RoomsDescription", $listing->RoomsDescription);
		$stmt->bindParam("SellingPrice", $listing->SellingPrice);
		$stmt->bindParam("Sewer", $listing->Sewer);
		$stmt->bindParam("Sprinkler", $listing->Sprinkler);
		$stmt->bindParam("SqFtofGuestHouse", $listing->SqFtofGuestHouse);
		$stmt->bindParam("Status", $listing->Status);
		$stmt->bindParam("Style", $listing->Style);
		$stmt->bindParam("View1", $listing->View1);
		$stmt->bindParam("Water", $listing->Water);
		$stmt->bindParam("WaterAccess", $listing->WaterAccess);
		$stmt->bindParam("WaterfrontDescription", $listing->WaterfrontDescription);
		$stmt->bindParam("WindowsTreatment", $listing->WindowsTreatment);
		$stmt->bindParam("YearBuilt", $listing->YearBuilt);
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$db = null;
		echo json_encode($listing); 
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function deleteListing($id) {
	$sql = "DELETE FROM mls WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$db = null;
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function findByName($query) {
	$sql = "SELECT * FROM listings WHERE UPPER(address) LIKE :query ORDER BY address";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);
		$query = "%".$query."%";  
		$stmt->bindParam("query", $query);
		$stmt->execute();
		$listings = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo json_encode($listings);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getConnection() {
	//$dbhost="127.0.0.1";
    $dbhost="localhost";
	$dbuser="root";
	$dbpass="06malloy13";
    // Home
	// $dbname="test";
    // Work
    $dbname="csv_db";
	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	return $dbh;
}

?>